In an environment where database users store both active and history data in related tables, the users often need to query and access data from both tables using certain criteria via static SQL (Structured Query Language) statements. One of criteria may be a specific time from the active table or the system temporal table (STT) and optionally from the table containing the history data (history table). The users intend to get data at different points of time from these temporal tables without changing the underneath static SQL statements. For example, in a scenario where a user has a packaged application, the user intends to run against the state of the business as of today, against the state of the business as of the end of last quarter, or against the state of the business as of last year. Changing the application and adding as of period specifications to each static SQL statement in the application may not be possible in case of the packaged application. This requirement is referred to as a “time machine” in a database management system where history data may need to be looked up in order to return all results.
To support such a requirement, a database management system needs to recompile the static SQL statement when the demanded time period is specified before execution, which significantly increases the SQL execution time. For example, the query is bound with access to the active table only. Then, at execution time, once the system detects that history data is needed, the query is incrementally rebound so that the history table is also looked up. This is an expensive process because each time the query is executed and the incremental bind process is activated.
Another alternative is to always prepare a bound form of the SQL statement with “UNION ALL” between the active and history data, and add a pruning predicate to optionally remove the history table look-up at the execution time. A drawback of this approach is that extra storage is needed for the bound form of the SQL (which includes “UNION ALL”) and the unnecessary time is needed for loading such extra storage into memory. The “UNION ALL” operation in a query is often a performance overhead and can affect other access path selections. When an instance of the execution of the packaged application does not require history data, the “UNION ALL” access plan is still in effect and can affect performance of the query.